package com.vs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.vs.model.ProjectForm;
import com.vs.util.Constants;
import com.vs.util.ProjectStatus;

public class ProjectOperations {

	private String insertString = "insert into projects(project_name,budget,timeline,start_date,end_date,project_details,added_by,status) values(?,?,?,?,?,?,?,?)";
	private String deleteString = "delete from projects where project_id=?";

	// private String getExpenseString =
	// "select expense_id,expense_type_id,expense_date,expense_amt,notes from expenses";
	// private String fetchString =
	// "select project_id,project_name,budget,timeline,start_date,end_date,project_details from projects ";
	private String getAllProjectsString = "call get_project_list() ";
	
	private String getProjectString = "select * from projects where project_id=? ";
	private String updateProjectString = "update projects set budget=?,timeline=?,start_date=?,end_date=?,project_details=?,modified_by=? where project_id=? ";
	
	private String lovString = "select project_id,project_name from projects ";

	public boolean deleteProject(Integer projectId) {
		Connection conn = null;
		boolean success = false;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(deleteString);
			ps.setInt(1, projectId);
			ps.execute();
			ps.close();
			conn.commit();
			success = true;

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return success;

	}

	public boolean addProject(ProjectForm projectForm) {
		Connection conn = null;
		boolean success = false;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(insertString);
			ps.setString(1, projectForm.getProjectName());
			ps.setString(2, projectForm.getProjectBudget());
			ps.setString(3, projectForm.getProjectTimeLine());
			ps.setString(4, projectForm.getPlannedStartDate());
			ps.setString(5, projectForm.getPlannedEndDate());
			ps.setString(6, projectForm.getProjectDetails());
			ps.setString(7, projectForm.getAddedBy());
			ps.setString(8, ProjectStatus.STARTED.toString());
			ps.execute();
			ps.close();
			conn.commit();
			success = true;

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return success;
	}
	
	public boolean editProject(ProjectForm projectForm) {
		Connection conn = null;
		boolean success = false;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(updateProjectString);
			ps.setString(1, projectForm.getProjectBudget());
			ps.setString(2, projectForm.getProjectTimeLine());
			ps.setString(3, projectForm.getPlannedStartDate());
			ps.setString(4, projectForm.getPlannedEndDate());
			ps.setString(5, projectForm.getProjectDetails());
			ps.setString(6, projectForm.getModifiedBy());
			ps.setInt(7, projectForm.getProjectId());
			ps.execute();
			ps.close();
			conn.commit();
			success = true;

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return success;
	}
	
	public ProjectForm getProject(Integer projectId) {
		Connection conn = null;
		System.out.println("getAllProjectList called");
		ProjectForm projectType = null;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(getProjectString);
			ps.setInt(1,projectId);
			// expenseTypeForm.getExpenseType());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				projectType = new ProjectForm();
				projectType.setProjectId(rs.getInt(1));
				projectType.setProjectName(rs.getString(2));
				projectType.setProjectBudget(rs.getString(3));
				projectType.setProjectTimeLine(rs.getString(4));
				projectType.setPlannedStartDate(rs.getString(5));
				projectType.setPlannedEndDate(rs.getString(6));
				projectType.setProjectDetails(rs.getString(7));
			}
			ps.close();
			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		Constants.log.info(projectType);
		return projectType;
	}

	public List<ProjectForm> getAllProjectList() {
		Connection conn = null;
		List<ProjectForm> projectList = new ArrayList<ProjectForm>();
		System.out.println("getAllProjectList called");
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(getAllProjectsString);
			// psExpenseTypeInsert.setString(1,
			// expenseTypeForm.getExpenseType());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				ProjectForm projectType = new ProjectForm();
				projectType.setProjectId(rs.getInt(1));
				projectType.setProjectName(rs.getString(2));
				projectType.setProjectBudget(rs.getString(3));
				projectType.setProjectTimeLine(rs.getString(4));
				projectType.setPlannedStartDate(rs.getString(5));
				projectType.setPlannedEndDate(rs.getString(6));
				projectType.setProjectDetails(rs.getString(7));
				projectList.add(projectType);
			}
			ps.close();
			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println("getAllProjectList size:" + projectList.size());
		return projectList;
	}

	public List<ProjectForm> getProjectLOV() {
		Connection conn = null;
		List<ProjectForm> projectList = new ArrayList<ProjectForm>();

		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(lovString);
			// psExpenseTypeInsert.setString(1,
			// expenseTypeForm.getExpenseType());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				ProjectForm projectType = new ProjectForm();
				projectType.setProjectId(rs.getInt(1));
				projectType.setProjectName(rs.getString(2));
				projectList.add(projectType);
			}
			ps.close();
			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println("size:" + projectList.size());
		return projectList;
	}

	public static void main(String args[]) {
		// List<ExpenseForm> expenseList = new ExpensesOperation()
		// .getExpensesList();
		// System.out.println("size:" + expenseList.size());

	}

}
